﻿using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Beryl.OrderControllers.DBUpdate
{
    public class proUpdate
    {
        private static proUpdate instance = null;
        private static readonly object uniqueLock = new object();
        public static proUpdate Instance
        {
            get
            {
                lock (uniqueLock)
                {
                    if (instance == null)
                    {
                        instance = new proUpdate();
                    }
                }
                return instance;
            }
        }
        public proUpdate() { }

        public void deleteProLot(Dictionary<string, Dictionary<string, Dictionary<string, string>>> proOrderObj)
        {
            List<string> sqlArr = new List<string>();
            try
            {
                string sqlString = "";
                foreach (var proOrderNo in proOrderObj.Keys)
                {
                    sqlString = string.Format(@"DELETE FROM APS_ProLotOrder WHERE cOrderNo = '{0}';", proOrderNo);
                    sqlArr.Add(sqlString);
                    foreach (var proLotOrderNo in proOrderObj[proOrderNo].Keys)
                    {
                        sqlArr.Add(string.Format("DELETE FROM APS_ValidRouting WHERE cOrderNo = '{0}';", proLotOrderNo));
                        sqlArr.Add(string.Format("DELETE FROM APS_ValidBOM WHERE cOrderNo = '{0}';", proLotOrderNo));
                        sqlArr.Add(string.Format("DELETE FROM APS_POLotandSaleORelat WHERE cMOLotNo = '{0}';", proLotOrderNo));
                    }
                }
                bool ok = CommonMethod.Instance.insertSqlList(sqlArr);
                if (!ok)
                {
                    throw new Exception("删除失败");
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        // 更新生产订单，批次订单
        public void updateProOrder(Dictionary<string, Dictionary<string, Dictionary<string, string>>> proOrderObj)
        {
            try
            {
                // 日期
                string today = CommonMethod.Instance.getTimes();
                // sql数组
                List<string> sqlArr = new List<string>();
                // sql语句
                string sqlString1 = "";
                string sqlString3 = "";
                string sqlString2 = "INSERT INTO APS_ProLotOrder(cOrderNo, cMOLotNo, cBegProDate, cStatusNo," +
                            "cStaUpdateTime, cStaUpdater, cMatNo, dLotQty, dDeliveryDate, cOFlag," +
                            "cProductDepNo, cCombination, cLotType, cBarCode, cMakeTime, Coefficient, putProQty) SELECT* FROM(VALUES ";
                string sqlString4 = "";
                JArray jsonArr = null;
                var pro_flag = new Dictionary<string, bool>();
                var proLot_flag = new Dictionary<string, bool>();
                foreach (var proOrderNo in proOrderObj.Keys)
                {
                    pro_flag.Add(proOrderNo, false);
                    foreach (var proLotOrderNo in proOrderObj[proOrderNo].Keys)
                    {
                        proLot_flag.Add(proLotOrderNo, false);
                        // 批次数量
                        float batchNum = float.Parse(proOrderObj[proOrderNo][proLotOrderNo]["batchNum"]);
                        // 生产部门编号
                        string cProductDepNo = proOrderObj[proOrderNo][proLotOrderNo]["cProductDepNo"].ToString();
                        // 交货期
                        string dDeliveryDate = proOrderObj[proOrderNo][proLotOrderNo]["dDeliveryDate"];
                        //if (proOrderObj[proOrderNo][proLotOrderNo].ContainsKey("listJson") || proOrderObj[proOrderNo][proLotOrderNo].ContainsKey("listJson1"))
                        //{
                           
                        //    var json = proOrderObj[proOrderNo][proLotOrderNo]["listJson"];
                        //    jsonArr = (JArray)JsonConvert.DeserializeObject(json);
                        //}
                        if (proOrderObj[proOrderNo][proLotOrderNo].ContainsKey("listJson"))
                        {
                            var json = proOrderObj[proOrderNo][proLotOrderNo]["listJson"];
                            jsonArr = (JArray)JsonConvert.DeserializeObject(json);
                        }
                        if (proOrderObj[proOrderNo][proLotOrderNo].ContainsKey("listJson1"))
                        {
                            var json = proOrderObj[proOrderNo][proLotOrderNo]["listJson1"];
                            jsonArr = (JArray)JsonConvert.DeserializeObject(json);
                        }
                        if (jsonArr.Count > 0)
                        {
                            for (int i = 0; i < jsonArr.Count; i++)
                            {
                                string loginUser = jsonArr[i]["loginUser"].ToString();
                                string Num = jsonArr[i]["Num"].ToString();
                                string cMatNo = jsonArr[i]["cMatNo"].ToString();
                                string dPlanQty = jsonArr[i]["dPlanQty"].ToString();
                                string cSourceNo = jsonArr[i]["cSourceNo"].ToString();
                                string cPOrderNo = jsonArr[i]["cPOrderNo"].ToString();
                                string cOFlag = jsonArr[i]["cOFlag"].ToString();
                                string cCombination = jsonArr[i]["cCombination"].ToString();
                                string cReworkFlag = jsonArr[i]["cReworkFlag"].ToString();
                                string cBigBegProDate = jsonArr[i]["cBigBegProDate"].ToString();
                                string iBatchMult = jsonArr[i]["iBatchMult"].ToString();
                                string cInOutType = jsonArr[i]["cInOutType"].ToString();
                                string Coefficient = jsonArr[i]["Coefficient"].ToString();
                                if (Coefficient != "")
                                {

                                }
                                else
                                {
                                    Coefficient = "1";
                                }
                                float putProQty = float.Parse(Num) * float.Parse(Coefficient);
                                string proLotOrderNoHalf = jsonArr[i]["proLotOrderNoHalf"].ToString();
                                float cProLotFlag = 0;
                                if (!pro_flag[proOrderNo])
                                {
                                    // 更新计划订单状态
                                    sqlString1 = string.Format(@"UPDATE APS_PlanOrder SET cStatusNo = '0050', cStaUpdateTime = '{0}' " +
                                           "WHERE cPOrderNo = '{1}';", today, cPOrderNo);
                                    sqlArr.Add(sqlString1);
                                    pro_flag[proOrderNo] = true;
                                }
                                // 更新批次订单表
                                string cBarCodes;
                                string cLotType;
                                if (!proLot_flag[proLotOrderNo])
                                {
                                    if (cInOutType == "output")
                                    {
                                        string cBarCodeHalf = jsonArr[i]["cBarCodeHalf"].ToString();
                                        cBarCodes = cBarCodeHalf;
                                    }
                                    else
                                    {
                                        string cBarCode = jsonArr[i]["cBarCode"].ToString();
                                        cBarCodes = cBarCode;
                                    }
                                    if (proLotOrderNoHalf != "")
                                    {
                                        cLotType = "2";
                                    }
                                    else
                                    {
                                        cLotType = "1";
                                    }
                                    if (float.Parse(cReworkFlag) != 0)
                                    {
                                        cLotType = "4";
                                    }
                                    sqlString4 += string.Format(@"('{0}','{1}','','0050','{2}','{3}','{4}','{5}','{6}'," +
                                        "'0','{7}','{8}','{9}','{10}','{11}','{12}','{13}'),", proOrderNo, proLotOrderNo, today, loginUser, cMatNo, batchNum, dDeliveryDate, cProductDepNo, cCombination, cLotType,
                                        cBarCodes, today, Coefficient, putProQty);
                                    proLot_flag[proLotOrderNo] = true;
                                }
                                // 更新批次与销售订单关系表
                                sqlString3 = string.Format(@"INSERT INTO APS_POLotandSaleORelat (cMOLotNo,cSourceNo,dQty) VALUES ('{0}','{1}', '{2}');", proLotOrderNo, cSourceNo, Num);
                                sqlArr.Add(sqlString3);
                                sqlArr.Add(string.Format(@"UPDATE APS_SOrder SET cStatusNo = '0050',cStaUpdateTime = '{0}' WHERE cSONo = '{1}' AND cStatusNo = '0020';", today, cSourceNo));
                            }
                        }
                    }
                }
                if (sqlString4 != "")
                {
                    sqlString4 = sqlString4.Substring(0, sqlString4.Length - 1);
                    sqlString2 += sqlString4 + ") A (Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17);";
                    sqlArr.Add(sqlString2);
                }
                bool ok = CommonMethod.Instance.insertSqlList(sqlArr);
                if (!ok)
                {
                    throw new Exception("删除失败");
                }
            }
            catch(Exception e)
            {
                throw e;
            }
        }

        /**
 * @description 插入批次物料总需求
 * @param {*}  proLotMatQtyObj
 */
        public void insertBatchMatTotalQty(Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>> proLotMatQtyObj)
        {
            List<string> sqlArr = new List<string>();
            string sqlString = "INSERT INTO APS_ValidBOM (cOrderNo,cOrderType,cMatNo,cOperationNo,cOperationName,cOpSeq,cInOutType,cMatandResNo,cMatandResName,cMatandResType,dwidth,dlength,cSpecifution,cUnit,dReqQty,cCapacityLow,cCapacityup,cCapacity,cBomid,cCateNo," +
                "dVoltage,Unit,cBatchNumber,dPlanQty,cSortSeq) SELECT * FROM ( VALUES ";
            string sqlString1 = "";
            float length1 = 0;
            //proLotMatQtyObj.TryGetValue("length", out Dictionary<string, Dictionary<string, Dictionary<string, string>>> para);

            float length2 = proLotMatQtyObj.Count;
            //foreach (var len in para.Keys)
            //{
            //    length2 = float.Parse(len);
            //}

            foreach (var cMOLotNo in proLotMatQtyObj.Keys)
            {
                string cOrderNo = cMOLotNo;
                if (cMOLotNo != "length")
                {
                    length1++;
                    foreach (var cMatNo in proLotMatQtyObj[cMOLotNo].Keys)
                    {
                        float length3 = 0;
                        float length4 = proLotMatQtyObj[cMOLotNo][cMatNo].Count;
                        if (length4 == 0)
                        {
                            continue;
                        }
                        foreach (var num in proLotMatQtyObj[cMOLotNo][cMatNo].Keys)
                        {
                            if (num != "length")
                            {
                                length3++;
                                string cOrderType = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cOrderType"];
                                // let ResNo = proLotMatQtyObj[cMOLotNo][cMatNo][num].ResNo;
                                string ResNo = cMatNo;
                                string cOperationNo = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cOperationNo"];
                                string cOperationName = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cOperationName"];
                                string cOpSeq = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cOpSeq"];
                                string cInOutType = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cInOutType"];
                                string tmp_proNo = num;
                                string cMatandResName = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cMatandResName"];
                                string cMatandResType = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cMatandResType"];
                                string dPlanQty = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cMatTotal"] == "" ? "NULL" : proLotMatQtyObj[cMOLotNo][cMatNo][num]["cMatTotal"];//
                                string dWidth = proLotMatQtyObj[cMOLotNo][cMatNo][num]["dWidth"] == "" ? "NULL" : proLotMatQtyObj[cMOLotNo][cMatNo][num]["dWidth"];//
                                string dLength = proLotMatQtyObj[cMOLotNo][cMatNo][num]["dLength"] == "" ? "NULL" : proLotMatQtyObj[cMOLotNo][cMatNo][num]["dLength"];//
                                string cSpecifution = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cSpecifution"];
                                string cUnit = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cUnit"];
                                string TotalQty = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cMatTotal"] == "" ? "NULL" : proLotMatQtyObj[cMOLotNo][cMatNo][num]["cMatTotal"];//
                                string cCapacityLow = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cCapacityLow"] == "" ? "NULL" : proLotMatQtyObj[cMOLotNo][cMatNo][num]["cCapacityLow"];//
                                string cCapacityUp = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cCapacityUp"] == "" ? "NULL" : proLotMatQtyObj[cMOLotNo][cMatNo][num]["cCapacityUp"];//
                                string cCapacity = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cCapacity"] == "" ? "NULL" : proLotMatQtyObj[cMOLotNo][cMatNo][num]["cCapacity"];//
                                string cBomid = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cBomid"];
                                string cCateNo = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cCateNo"];
                                string dVoltage = proLotMatQtyObj[cMOLotNo][cMatNo][num]["dVoltage"] == "" ? "NULL" : proLotMatQtyObj[cMOLotNo][cMatNo][num]["dVoltage"];//
                                string Unit = proLotMatQtyObj[cMOLotNo][cMatNo][num]["Unit"];
                                string cBatchNumber = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cBatchNumber"];
                                string cSortSeq = proLotMatQtyObj[cMOLotNo][cMatNo][num]["cSortSeq"];

                                if ((length1 == length2) && (length3 == length4))
                                {
                                    sqlString1 += string.Format(@"('{0}','{1}','{2}','{3}','{4}','{5}','{6}'," +
                                "'{7}','{8}','{9}',{10},{11},'{12}','{13}',{14}," +
                                "{15},{16},{17},'{18}','{19}',{20},NULLIF('{21}','null'),'{22}'," +
                                "{23},'{24}'),", cOrderNo, cOrderType, ResNo, cOperationNo, cOperationName, cOpSeq, cInOutType, tmp_proNo, cMatandResName,
                                cMatandResType, dWidth, dLength, cSpecifution, cUnit, TotalQty, cCapacityLow, cCapacityUp, cCapacity, cBomid, cCateNo,
                                dVoltage, Unit, cBatchNumber, dPlanQty, cSortSeq);
                                }
                                else
                                {
                                    sqlString1 += string.Format(@"('{0}','{1}','{2}','{3}','{4}','{5}','{6}'," +
                                "'{7}','{8}','{9}',{10},{11},'{12}','{13}',{14}," +
                                "{15},{16},{17},'{18}','{19}',{20},NULLIF('{21}','null'),'{22}'," +
                                "{23},'{24}'),", cOrderNo, cOrderType, ResNo, cOperationNo, cOperationName, cOpSeq, cInOutType, tmp_proNo, cMatandResName,
                                cMatandResType, dWidth, dLength, cSpecifution, cUnit, TotalQty, cCapacityLow, cCapacityUp, cCapacity, cBomid, cCateNo,
                                dVoltage, Unit, cBatchNumber, dPlanQty, cSortSeq);
                                }
                            }
                        }
                    }
                }
            }
            // 数据库更新
            if (sqlString1 != "")
            {
                sqlString1 = sqlString1.Substring(0, sqlString1.Length - 1);
                sqlString += sqlString1 + ") A (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12," +
                "Col13, Col14, Col15, Col16, Col17, Col18, Col19, Col20, Col21, Col22, Col23, Col24, Col25);";

                sqlArr.Add(sqlString);
            }
            bool ok = CommonMethod.Instance.insertSqlList(sqlArr);
            if (!ok)
            {
                throw new Exception("插入批次物料总需求失败");
            }
        }

        /**
        * @description 插入批次工艺路线
        * @param {*}  proRoutingObj
        */
        public void insertProRouting(Dictionary<string, Dictionary<string, Dictionary<string, Dictionary<string, string>>>> proRoutingObj)
        {
            //日期
            string today = CommonMethod.Instance.getTime3();
            List<string> sqlArr = new List<string>();
            string sqlString = "INSERT INTO APS_ValidRouting(cOrderNo, cOrderType, cMatNo, cOperationNo, cOperationName, cOpSeq, dTransferTime) SELECT * FROM ( VALUES ";
            string sqlString1 = "";
            float length1 = 0;
            float length2 = proRoutingObj.Count;

            foreach (var cOrderNo in proRoutingObj.Keys)
            {
                length1 = length1 + 1;
                float length3 = 0;
                float length4 = proRoutingObj[cOrderNo].Count;
                foreach (var cMatNo in proRoutingObj[cOrderNo].Keys)
                {
                    length3 = length3 + 1;
                    float length5 = 0;
                    float length6 = proRoutingObj[cOrderNo][cMatNo].Count;
                    foreach (var cOperationNo in proRoutingObj[cOrderNo][cMatNo].Keys)
                    {
                        length5++;
                        string cOrderType = proRoutingObj[cOrderNo][cMatNo][cOperationNo]["cOrderType"];
                        string cOperationName = proRoutingObj[cOrderNo][cMatNo][cOperationNo]["cOperationName"];
                        string cOpSeq = proRoutingObj[cOrderNo][cMatNo][cOperationNo]["cOpSeq"];
                        //let dTransferTime = proRoutingObj[cOrderNo][cMatNo][cOperationNo].dTransferTime;
                        float dTransferTime = 0;

                        if ((length1 == length2) && (length3 == length4) && (length5 == length6))
                        {
                            sqlString1 += string.Format(@"('{0}','{1}','{2}','{3}','{4}','{5}',{6})", cOrderNo, cOrderType, cMatNo, cOperationNo, cOperationName, cOpSeq, dTransferTime);
                        }
                        else
                        {
                            sqlString1 += string.Format(@"('{0}','{1}','{2}','{3}','{4}','{5}',{6}),", cOrderNo, cOrderType, cMatNo, cOperationNo, cOperationName, cOpSeq, dTransferTime);
                        }
                    }
                }
            }
            // 数据库更新
            if (sqlString1 != "")
            {
                sqlString += sqlString1 + ") A (Col1,Col2,Col3,Col4,Col5,Col6,Col7);";
                sqlArr.Add(sqlString);
            }
            bool ok = CommonMethod.Instance.insertSqlList(sqlArr);
            if (!ok)
            {
                throw new Exception("插入批次工艺路线失败");
            }
        }
    }
}